﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class FuelMachinasObject
    {
        public const string sql = @"
SELECT 'Закуплено топливо (всего):' as Итоги, '' as Машина, '' as Топливо, '' as Литры, '' as Пробег, '' as Расход, '' as Формула
UNION ALL
SELECT 	'' as Итоги,
		'---' as Машина,
		DictionaryCategories.Name as Топливо,
		convert(nchar(20), sum(t.Liters)) as Литры,
		'' as Пробег,
		'' as Расход,
		'' as Формула
FROM Transactions as t
LEFT JOIN DictionaryCategories on t.Category = DictionaryCategories.ID
WHERE t.Category in (select id from DictionaryCategories where Fuel = 1)
GROUP BY DictionaryCategories.Name

UNION ALL
SELECT 'Заправлено из своей АЗС с разбивкой по машинам:' as Итоги, '' as Машина, '' as Топливо, '' as Литры, '' as Пробег, '' as Расход, '' as Формула
UNION ALL

SELECT	'' as Итоги,
		DictionaryMachines.Name as Машина,
		DictionaryCategories.Name as Топливо,
		convert(nchar(20), total.Liters) as Литры,
		convert(nchar(20), w.Distance) as Пробег,
		convert(nchar(20), (total.Liters * 100 / w.Distance)) as Расход,
		'Литры x 100 / Пробег(км)' as Формула
FROM (SELECT Machine, Fuel, sum(r.Liters) as Liters FROM Refueling as r WHERE r.FuelOperType = 1 GROUP BY Machine, Fuel) AS total
LEFT JOIN (select Machine, sum(Distance) as Distance from Work group by Machine) as w on total.Machine = w.Machine
LEFT JOIN DictionaryMachines on total.Machine = DictionaryMachines.ID
LEFT JOIN DictionaryCategories on total.Fuel = DictionaryCategories.ID
";
        public string Итоги { get; set; }
        public string Машина { get; set; }
        public string Топливо { get; set; }
        public string Литры { get; set; }
        public string Пробег { get; set; }
        public string Расход { get; set; }
        public string Формула { get; set; }
    }
}
